/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package assignment_cp3120.DAO.mysqlDAO;

import assignment_cp3120.DAO.FlightDAO;
import assignment_cp3120.dataObjects.Flight;
import java.sql.Connection;
import java.sql.Date;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Vector;

/**
 * A class that holds the functions that preform mysql querys.
 * @author Richard Irons
 */
public class MysqlFlightDAO implements FlightDAO {
    /**
     * A function that returns an arraylist of all of the flights that are still avalible.
     * The avalible flights are currnetly those that are from the given date onwards.
     * --- Support needs to be added to allow for the flights that are still avalible from the time.
     * @param date
     * Avalible flights are this date onwards. 
     * @return
     * Flights from the DB after and including the current date.
     */
    public Vector<Flight> getFlightsFromDate(Date date){
        Vector<Flight> flights = new Vector<Flight>();
        try{            
            Connection c = MysqlDA0Factory.createConnection();
            Statement statement = c.createStatement();
            ResultSet rs = statement.executeQuery(
                    "SELECT * FROM FLIGHT WHERE FLIGHT_DATE >=\""
                    + date 
                    + "AND CAPACITY > COUNT(SELECT FLIGHT_ID FROM TICKETS WHERE FLIGHT.FLIGHT_ID = TICKET.FLIGHT_ID GROUP BY FLIGHT_ID)"
                    + "\" ORDER BY FLIGHT_DATE");
            while(rs.next()) {
                flights.add(getFlightFromRS(rs));
            }
        }catch(SQLException e){System.out.println(e.getMessage());}

        return flights;  
    }
    public Vector<Flight> getFlights(){
        Vector<Flight> flights = new Vector<Flight>();
        try{            
            Connection c = MysqlDA0Factory.createConnection();
            Statement statement = c.createStatement();
            ResultSet rs = statement.executeQuery(
                    "SELECT * FROM FLIGHT " +
                    "ORDER BY FLIGHT_ID");
            while(rs.next()){
                flights.add(getFlightFromRS(rs));
            }
        }catch(SQLException e){System.out.println(e.getMessage());}
        return flights;  
    }
    /**
     * Take a result set and get the date from it and assign it to a temp flight.
     * Return the flight
     * @param rs
     * Result set get the flight data from
     * @return
     * Flight
     */
    private Flight getFlightFromRS(ResultSet rs){
        Flight flight = new Flight();        
        try{
            flight.setFlight_id(rs.getString("FLIGHT_ID"));
            flight.setDep_id(rs.getString("DEP_ID"));
            flight.setDest_id(rs.getString("DEST_ID"));
            flight.setFlight_date(rs.getDate("FLIGHT_DATE"));
            flight.setDep_time(rs.getTime("DEP_TIME"));
            flight.setArr_time(rs.getTime("ARR_TIME"));
            flight.setCapacity(rs.getInt("CAPACITY"));
            flight.setFlight_price(rs.getInt("FLIGHT_PRICE"));
        }catch(SQLException e){
            System.out.println(e.getMessage());
        }
        return flight;
    }
    /**
     * Add the flight to the database
     * @param flight - flight to be added
     * @return
     * true if all went well, false if an sqlexception occured.
     */
    public int createFlight(Flight flight) {
        Connection c = MysqlDA0Factory.createConnection();
        int result = 0;
        try{
            Statement statment = c.createStatement();
            result = statment.executeUpdate(
                    "INSERT INTO 3120S109g09.FLIGHT ( "
                    +"FLIGHT_ID" + " , "
                    +"DEP_ID" + " , "
                    +"DEST_ID" + " , "
                    +"FLIGHT_DATE" + " , "
                    +"DEP_TIME" + " , "
                    +"ARR_TIME" + " , "
                    +"CAPACITY" + " , "
                    +"FLIGHT_PRICE"
                    + ") VALUES ( "
                    + "\"" + flight.getFlight_id() + "\" , "
                    + "\"" + flight.getDep_id() + "\" , "
                    + "\"" + flight.getDest_id() + "\" , "
                    + "\"" + flight.getFlight_date() + "\" , "
                    + "\"" + flight.getDep_time() + "\" , "
                    + "\"" + flight.getArr_time() + "\" , "
                    + "\"" + flight.getCapacity() + "\" , "
                    + "\"" + flight.getFlight_price() + "\" "
                    + ")");

        }catch(SQLException e){
            System.out.println(e.getMessage());
        }
        return result;
    }
    /**
     * delete the flight with the given flight id from the database.
     * @param flight_id - flight to be deleted
     * @return
     * true if deleted, false if a mysql exception occured.
     */
    public int deleteFlight(String flight_id) {
        Connection c = MysqlDA0Factory.createConnection();
        int result = 0;
        try{
            Statement statment = c.createStatement();
            result = statment.executeUpdate(
                    "DELETE FROM `FLIGHT` WHERE FLIGHT_ID = \""
                    +flight_id
                    +"\" LIMIT 1 ");

        }catch(SQLException e){
            System.out.println(e.getMessage());
        }
        return result;
    }
}